/**
 * 
 */
package com.seascape.swms.framework.beetsql;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.beetl.sql.core.BeetlSQLException;
import org.beetl.sql.core.SQLReady;
import org.beetl.sql.core.query.GroupBy;
import org.beetl.sql.core.query.OrderBy;

/**
 * @author Lay
 *
 */
public class ViewQuery {

	private StringBuilder whereSql;

	private StringBuilder viewSql;

	private String columns;
	
	private View view;

	private List<Object> args = new ArrayList<Object>();

	private OrderBy orderBy = null;
	private GroupBy groupBy = null;

	public final String AND = "AND";
	public final String OR = "OR";
	public final String WHERE = "WHERE";
	public final String IN = "IN";
	public final String NOT_IN = "NOT IN";
	public final String BETWEEN = "BETWEEN";
	public final String NOT_BETWEEN = "NOT BETWEEN";

	public ViewQuery(View view) {
		this.view = view;
	}

	public ViewQuery columns(String columns){
		this.columns = columns;
		return this;
	}
	
	private StringBuilder buildSql(String sql) {
		this.viewSql = new StringBuilder();
		if(columns==null){
			this.viewSql.append("SELECT * FROM (").append(sql)
			.append(")");
		}else{
			this.viewSql.append("SELECT ").append(columns).append(" FROM (").append(sql)
			.append(")");
		}
		return viewSql;
	}
	
	private StringBuilder getViewSql(){
		return buildSql(view.getSql());
	}
	
	private StringBuilder getViewSql(Class clazz){
		return buildSql(view.getSqlByClass(clazz));
	}
	private SQLReady sqlReady(Map<String, Object> params,String sql) {
		StringBuilder sb = new StringBuilder(sql);
		if (params != null && params.size() > 0) {
			sb.append(" A WHERE 1=1");
			Object[] args = new Object[params.size()];
			int i = 0;
			for (Entry<String, Object> entry : params.entrySet()) {
				sb.append(" AND ").append(entry.getKey()).append("=?");
				args[i] = entry.getValue();
				i++;
			}
			return new SQLReady(sb.toString(), args);
		}
		return new SQLReady(sb.toString());
	}
	public SQLReady sqlReady(Map<String, Object> params) {
		return sqlReady(params,getViewSql().toString());
	}
	public SQLReady sqlReady(Map<String, Object> params,Class clazz) {
		return sqlReady(params,getViewSql(clazz).toString());
	}

	private SQLReady sqlReady(String sql) {
		StringBuilder sb = new StringBuilder(sql);
		if (whereSql != null) {
			sb.append(getAdditionalPartSql());
			return new SQLReady(sb.toString(), args.toArray());
		}
		return new SQLReady(sb.toString());
	}
	
	public SQLReady sqlReady() {
		return sqlReady(getViewSql().toString());
	}
	
	public SQLReady sqlReady(Class clazz) {
		return sqlReady(getViewSql(clazz).toString());
	}

	protected void clear() {
		orderBy = null;
		groupBy = null;
	}

	/**
	 * 
	 * @param sqlPart
	 * @return
	 */
	public ViewQuery appendSql(String sqlPart) {
		getWhereSql().append(sqlPart);
		return this;
	}

	protected void appendAndSql(String column, Object value, String opt) {
		appendSqlBase(column, value, opt, AND);
	}

	protected void appendOrSql(String column, Object value, String opt) {
		appendSqlBase(column, value, opt, OR);
	}

	protected void appendSqlBase(String column, Object value, String opt,
			String link) {
		if (getWhereSql().indexOf(WHERE) < 0) {
			link = WHERE;
		}
		this.appendSql(" ").appendSql(link).appendSql(" ").appendSql(column)
				.appendSql(opt);
		if (value != null) {
			this.appendSql(" ? ");
			args.add(value);
		}
	}

	protected void appendInSql(String column, Collection<?> value, String opt,
			String link) {
		if (getWhereSql().indexOf(WHERE) < 0) {
			link = WHERE;
		}
		this.appendSql(link).appendSql(column).appendSql(opt).appendSql(" ( ");
		for (Object o : value) {
			this.appendSql(" ? ,");
			args.add(o);
		}
		this.getWhereSql().deleteCharAt(this.getWhereSql().length() - 1);
		this.appendSql(" ) ");
	}

	protected void appendBetweenSql(String column, String opt, String link,
			Object... value) {
		if (getWhereSql().indexOf(WHERE) < 0) {
			link = WHERE;
		}

		this.appendSql(link).appendSql(column).appendSql(opt)
				.appendSql(" ? AND ? ");

		args.add(value[0]);
		args.add(value[1]);
	}

	public ViewQuery andEq(String column, Object value) {
		appendAndSql(column, value, "=");
		return this;
	}

	public ViewQuery andNotEq(String column, Object value) {
		appendAndSql(column, value, "<>");
		return this;
	}

	public ViewQuery andGreat(String column, Object value) {
		appendAndSql(column, value, ">");
		return this;
	}

	public ViewQuery andGreatEq(String column, Object value) {
		appendAndSql(column, value, ">=");
		return this;
	}

	public ViewQuery andLess(String column, Object value) {
		appendAndSql(column, value, "<");
		return this;
	}

	public ViewQuery andLessEq(String column, Object value) {
		appendAndSql(column, value, "<=");
		return this;
	}

	public ViewQuery andLike(String column, String value) {
		appendAndSql(column, value, " LIKE ");
		return this;
	}

	public ViewQuery andNotLike(String column, String value) {
		appendAndSql(column, value, " NOT LIKE ");
		return this;
	}

	public ViewQuery andIsNull(String column) {
		appendAndSql(column, null, " IS NULL ");
		return this;
	}

	public ViewQuery andIsNotNull(String column) {
		appendAndSql(column, null, " IS NOT NULL ");
		return this;
	}

	public ViewQuery andIn(String column, Collection<?> value) {
		appendInSql(column, value, IN, AND);
		return this;
	}

	public ViewQuery andNotIn(String column, Collection<?> value) {
		appendInSql(column, value, NOT_IN, AND);
		return this;
	}

	public ViewQuery andBetween(String column, Object value1, Object value2) {
		appendBetweenSql(column, BETWEEN, AND, value1, value2);
		return this;
	}

	public ViewQuery andNotBetween(String column, Object value1,
			Object value2) {
		appendBetweenSql(column, NOT_BETWEEN, AND, value1, value2);
		return this;
	}

	public ViewQuery orEq(String column, Object value) {
		appendOrSql(column, value, "=");
		return this;
	}

	public ViewQuery orNotEq(String column, Object value) {
		appendOrSql(column, value, "<>");
		return this;
	}

	public ViewQuery orGreat(String column, Object value) {
		appendOrSql(column, value, ">");
		return this;
	}

	public ViewQuery orGreatEq(String column, Object value) {
		appendOrSql(column, value, ">=");
		return this;
	}

	public ViewQuery orLess(String column, Object value) {
		appendOrSql(column, value, "<");
		return this;
	}

	public ViewQuery orLessEq(String column, Object value) {
		appendOrSql(column, value, "<=");
		return this;
	}

	public ViewQuery orLike(String column, String value) {
		appendOrSql(column, value, "LIKE");
		return this;
	}

	public ViewQuery orNotLike(String column, String value) {
		appendOrSql(column, value, "NOT LIKE");
		return this;
	}

	public ViewQuery orIsNull(String column) {
		appendOrSql(column, null, "IS NULL");
		return this;
	}

	public ViewQuery orIsNotNull(String column) {
		appendOrSql(column, null, "IS NOT NULL");
		return this;
	}

	public ViewQuery orIn(String column, Collection<?> value) {
		appendInSql(column, value, IN, OR);
		return this;
	}

	public ViewQuery orNotIn(String column, Collection<?> value) {
		appendInSql(column, value, NOT_IN, OR);
		return this;
	}

	public ViewQuery orBetween(String column, Object value1, Object value2) {
		appendBetweenSql(column, BETWEEN, OR, value1, value2);
		return this;
	}

	public ViewQuery orNotBetween(String column, Object value1,
			Object value2) {
		appendBetweenSql(column, NOT_BETWEEN, OR, value1, value2);
		return this;
	}

	public StringBuilder getWhereSql() {
		if (this.whereSql == null) {
			this.whereSql = new StringBuilder("");
		}
		return this.whereSql;
	}

	private StringBuilder getAdditionalPartSql() {
		StringBuilder sb = new StringBuilder(this.getWhereSql());
		if (this.orderBy != null) {
			sb.append(orderBy.getOrderBy()).append(" ");
		}

		if (this.groupBy != null) {
			sb.append(groupBy.getGroupBy()).append(" ");
		}
		return sb;
	}

	public ViewQuery having(ViewQuery condition) {
		int i = condition.getWhereSql().indexOf(WHERE);
		if (i > -1) {
			condition.getWhereSql().delete(i, i + 5);
		}
		if (this.groupBy == null) {
			throw new BeetlSQLException(BeetlSQLException.QUERY_SQL_ERROR);
		}
		groupBy.addHaving(condition.getWhereSql().toString());
		args.add(condition.getArgs());
		return this;
	}

	public ViewQuery groupBy(String column) {
		GroupBy groupBy = getGroupBy();
		groupBy.add(column);
		return this;
	}

	public ViewQuery orderBy(String orderBy) {
		OrderBy orderByInfo = this.getOrderBy();
		orderByInfo.add(orderBy);
		return this;
	}

	public ViewQuery asc(String column) {
		OrderBy orderByInfo = this.getOrderBy();
		orderByInfo.add(column + " ASC");
		return this;
	}

	public ViewQuery desc(String column) {
		OrderBy orderByInfo = this.getOrderBy();
		orderByInfo.add(column + " DESC");
		return this;
	}

	private OrderBy getOrderBy() {
		if (this.orderBy == null) {
			orderBy = new OrderBy();
		}
		return this.orderBy;
	}

	private GroupBy getGroupBy() {
		if (this.groupBy == null) {
			groupBy = new GroupBy();
		}
		return this.groupBy;
	}

	public List<Object> getArgs() {
		return args;
	}

}
